Newark City 4311

Newark City 4311 data anaysis

# Required libs
library(httr)
library(jsonlite)
library(lubridate)
library(tidyverse)
library(stringr)
library(leaflet)
#install.packages("DT")
library(rpart)
library(DT)
library(forecast)
library(rpart.plot)
library(caret)

Parsing Newark Historic data

urls.hist = c("http://data.ci.newark.nj.us/mk/api/3/action/datastore_search?resource_id=1cebcf44-3b2c-4f60-9d5d-817cb35bf9df&limit=40000",
              "http://data.ci.newark.nj.us/mk/api/3/action/datastore_search_sql?sql=SELECT%20*%20from%20%221cebcf44-3b2c-4f60-9d5d-817cb35bf9df%22%20WHERE%20_id%20%3E%2031889",
              "http://data.ci.newark.nj.us/mk/api/3/action/datastore_search_sql?sql=SELECT%20*%20from%20%221cebcf44-3b2c-4f60-9d5d-817cb35bf9df%22%20WHERE%20_id%20%3E%2063889",
              "http://data.ci.newark.nj.us/mk/api/3/action/datastore_search_sql?sql=SELECT%20*%20from%20%221cebcf44-3b2c-4f60-9d5d-817cb35bf9df%22%20WHERE%20_id%20%3E%2095888")
xhist = data.frame()
for(i in urls.hist ){
  raw.result <- GET(url = i)
  this.raw.content <- rawToChar(raw.result$content)
  this.content <- fromJSON(this.raw.content)
  json_file <- lapply(this.content, function(x) {
    x[sapply(x, is.null)] <- NA
    unlist(x)
  })
  
  xhistoric = this.content$result$records
  xhistoric = xhistoric[,c("Type Name","Type Id","Street Address" , "Longitude","Request Id","Request Date", "Latitude" , "_id" ,"ID")]
  xhist = rbind(xhist,xhistoric)
}
dim(xhist)

[1] 107614      9

Parsing 2015 Data

url = "http://data.ci.newark.nj.us/mk/api/3/action/datastore_search?resource_id=bc8e29eb-3c53-418c-a42a-4dbbaae668c2&limit=60000"
raw.result <- GET(url = url)
this.raw.content <- rawToChar(raw.result$content)
this.content <- fromJSON(this.raw.content)
json_file <- lapply(this.content, function(x) {
  x[sapply(x, is.null)] <- NA
  unlist(x)
})
x2015 = this.content$result$records
dim(x2015)

[1] 14618    10

Parsing 2018 Data

urls.2018 = c("http://data.ci.newark.nj.us/mk/api/3/action/datastore_search?resource_id=b923c1ad-7246-400b-a593-785f67677b94&limit=68408",
              "http://data.ci.newark.nj.us/mk/api/3/action/datastore_search_sql?sql=SELECT%20*%20from%20%22b923c1ad-7246-400b-a593-785f67677b94%22%20WHERE%20_id%20%3E%2031993",
              "http://data.ci.newark.nj.us/mk/api/3/action/datastore_search_sql?sql=SELECT%20*%20from%20%22b923c1ad-7246-400b-a593-785f67677b94%22%20WHERE%20_id%20%3E%2066495")
x2018 = data.frame()
for(i in urls.2018 ){
  raw.result <- GET(url = i)
  this.raw.content <- rawToChar(raw.result$content)
  this.content <- fromJSON(this.raw.content)
  json_file <- lapply(this.content, function(x) {
    x[sapply(x, is.null)] <- NA
    unlist(x)
  })
  
  xtemp = this.content$result$records
  xtemp = xtemp[,c("Complaint","DateCreated","Typename","Location","Lot", "ComplaintID","Department" ,"_id" ,"Block")]
  x2018 = rbind(x2018,xtemp)
}
dim(x2018)

[1] 71685     9

names(xhist)

[1] "Type Name"      "Type Id"        "Street Address" "Longitude"      "Request Id"     "Request Date"   "Latitude"      
[8] "_id"            "ID"            

names(x2015)

 [1] "Type Name"     "Type Id"       "Street Name"   "Longitude"     "Request Id"    "Request Date"  "Street Number" "Latitude"     
 [9] "_id"           "ID"           

names(x2018)

[1] "Complaint"   "DateCreated" "Typename"    "Location"    "Lot"         "ComplaintID" "Department"  "_id"         "Block"      

Fixing the 2015 dataset

x2015$`Street Address` = paste(x2015$`Street Number`,x2015$`Street Name`)
x2015$`Street Name` = NULL
x2015$`Street Number` = NULL

sum(is.na(x2018))

[1] 4075

sum(complete.cases(x2018))

[1] 70259

Assign Blank cells with NA’s for x2015

x2015$`Street Address`[x2015$`Street Address` == " "] = NA
sum(is.na(x2015))

[1] 2070

Assign Blank cells with NA’sfor x2018

xhist[xhist == " "] = NA
sum(is.na(xhist))

[1] 3862

Parse Date

xhist$`Request Date` = as.POSIXct(xhist$`Request Date`)
x2015$`Request Date` = "2015"
x2015$`Request Date` = as.Date(x2015$`Request Date`,format = "%Y")
x2018$DateCreated = as.POSIXct(x2018$DateCreated, format = "%b %d %Y")

Creating new combined dataset for x2015 and xhist

till.2015 = rbind(xhist,x2015)

names(till.2015)[1:3]<-c("Complaint","ComplaintID","Location")
names(till.2015)[6]<-c("DateCreated")

overall4311 Dataset with common attributes

overall4311 = rbind(till.2015[,c(1,3,6)], x2018[,c(1,4,2)])

fillColor = "#FFA07A"
fillColor2 = "#F1C40F"

x2018 %>%
  group_by(Typename) %>%
  summarise(Count = n()) %>%
  ungroup() %>%
  mutate(complaint_description = reorder(Typename,Count)) %>%
  arrange(desc(Count)) %>%
  head(10) %>%
  
  ggplot(aes(x = reorder(Typename,Count),y = Count )) +
  geom_bar(stat='identity',colour="white", fill = fillColor2) +
  geom_text(aes(x = complaint_description, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'Complaint Descriptors', 
       y = 'Count', 
       title = 'Highest Complaint Typename 2018') +
  coord_flip() + 
  theme_bw()

Combing the datasets for getting overall Complaint typename

temp = c(xhist$`Type Name`,x2015$`Type Name`,x2018$Typename)
m1 <- matrix(temp, ncol=1, byrow=TRUE)
overall.complaints <- as.data.frame(m1, stringsAsFactors=FALSE)
overall.complaints

dim(overall.complaints)

[1] 193917      1

overall.complaints %>%
  group_by(V1) %>%
  summarise(Count = n()) %>%
  ungroup() %>%
  mutate(complaint_description = reorder(V1,Count)) %>%
  arrange(desc(Count)) %>%
  head(20) %>%
  
  ggplot(aes(x = reorder(V1,Count),y = Count )) +
  geom_bar(stat='identity',colour="white", fill = fillColor2) +
  geom_text(aes(x = complaint_description, y = 1, label = paste0("(",Count,")",sep="")),
            hjust=0, vjust=.5, size = 4, colour = 'black',
            fontface = 'bold') +
  labs(x = 'Complaint Descriptors', 
       y = 'Count', 
       title = 'Highest Complaint Typename') +
  coord_flip() + 
  theme_bw()

Cluster Analysis of Complaints through Location

till.2015$Longitude = as.numeric(till.2015$Longitude)
till.2015$Latitude =  as.numeric(till.2015$Latitude)
center_lon = median(till.2015$Longitude,na.rm = TRUE)
View(m1)
center_lat = median(till.2015$Latitude,na.rm = TRUE)

till.2015 %>% leaflet() %>%addProviderTiles("Esri.NatGeoWorldMap") %>%
  
  addMarkers(lng = ~Longitude, lat = ~Latitude,clusterOptions = markerClusterOptions()) %>%
    # controls
  
  setView(lng=center_lon, lat=center_lat,zoom = 12) 

names(xhist)[6] = "DateCreated"

Trend of 4311 calls

xhist %>%
  mutate(year = format(DateCreated, format="%Y") )%>%
  mutate(month = format(DateCreated, format="%m")) %>%
  filter(!is.na(year)) %>%
  filter(!is.na(month)) %>%
  group_by(year,month) %>%
  summarise(Count = n()) %>%
  arrange(year,month) %>%
  mutate(YearMonth = make_date(year=year,month=month) ) %>%
  
  ggplot(aes(x=YearMonth,y=Count)) +
  geom_line(size=1, color="red")+
  geom_point(size=3, color="red") +
  labs(x = 'Time', y = 'Count',title = 'Trend of 4311 Calls') +
  theme_bw()

Newark4311TrendData = xhist %>%
  mutate(year = format(DateCreated, format="%Y") ) %>%
  mutate(month = format(DateCreated, format="%m")) %>%
  filter(!is.na(year)) %>%
  filter(!is.na(month)) %>%
  group_by(year,month) %>%
  summarise(Count = n()) %>%
  arrange(year,month)
tsNewark4311TrendData = ts(Newark4311TrendData)
datatable((tsNewark4311TrendData), style="bootstrap", class="table-condensed", options = list(dom = 'tp',scrollX = TRUE))

fit <- auto.arima(tsNewark4311TrendData[,3])
preds = forecast(fit, h = 5)
preds %>% autoplot(include=40) +theme_bw()

till.2015 %>%
  mutate(month = format(DateCreated, format="%m")) %>%
  filter(!is.na(month)) %>%
  group_by(month) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(month = reorder(month,Count)) %>%
  
    ggplot(aes(x = month,y = Count)) +
    geom_bar(stat='identity',colour="white", fill = fillColor2) +
    geom_text(aes(x = month, y = 1, label = paste0("(",Count,")",sep="")),
              hjust=0, vjust=.5, size = 4, colour = 'black',
              fontface = 'bold') +
    labs(x = 'Month', 
         y = 'Count', 
         title = 'Months with service requests counts') +
    coord_flip() + 
    theme_bw()

x2018 %>%
  mutate(year = format(DateCreated, format="%Y") )%>%
  mutate(month = format(DateCreated, format="%m")) %>%
  filter(!is.na(year)) %>%
  filter(!is.na(month)) %>%
  group_by(year,month) %>%
  summarise(Count = n()) %>%
  arrange(year,month) %>%
  mutate(YearMonth = make_date(year=year,month=month) ) %>%
  
  ggplot(aes(x=YearMonth,y=Count)) +
  geom_line(size=1, color="red")+
  geom_point(size=3, color="red") +
  labs(x = 'Time', y = 'Count',title = 'Trend of 4311 Calls') +
  theme_bw()
View(till.2015)

x2018 %>%
  mutate(month = format(DateCreated, format="%m")) %>%
  filter(!is.na(month)) %>%
  group_by(month) %>%
  summarise(Count = n()) %>%
  arrange(desc(Count)) %>%
  ungroup() %>%
  mutate(month = reorder(month,Count)) %>%
  
    ggplot(aes(x = month,y = Count)) +
    geom_bar(stat='identity',colour="white", fill = fillColor2) +
    geom_text(aes(x = month, y = 1, label = paste0("(",Count,")",sep="")),
              hjust=0, vjust=.5, size = 4, colour = 'black',
              fontface = 'bold') +
    labs(x = 'Month', 
         y = 'Count', 
         title = 'Months with service requests counts') +
    coord_flip() + 
    theme_bw()

na.omit(till.2015) %>%
  group_by(Location) %>%
  summarise(Count = length(Location)) %>%
  arrange(desc(Count), .by_group = TRUE)

NA

Location Analisis of 2018

na.omit(x2018) %>%
  group_by(Location) %>%
  summarise(Count = length(Location)) %>%
  arrange(desc(Count), .by_group = TRUE)

NA

Departmants in 2018

x2018 %>%
  group_by(Typename,Department) %>%
  summarise(Count = n()) %>%
  ungroup() %>%
  mutate(complaint_description = reorder(Department,Count)) %>%
  arrange(desc(Count), .by_group = TRUE) %>%
  head(50) %>%
  
  ggplot(aes(x = reorder(Department,Count),y = Count)) +
  geom_bar(stat='identity',colour="white", fill = fillColor) +
  labs(x = 'Agencies', 
       y = 'Complaint Count', 
       title = 'Number of Comlpaints With Agency') +
  coord_flip() + 
  theme_bw()

na.omit(x2018) %>%
  group_by(Location,Typename) %>%
  summarise(Count = length(Location)) %>%
  arrange(desc(Count), .by_group = TRUE)

NA

na.omit(x2018) %>%
  group_by(Location,Typename)  %>%
  summarise(Count = length(Typename)) %>%
  arrange(desc(Count), .by_group = TRUE)

Newark4311SampleAll = till.2015 %>%
  filter(!is.na(Latitude) ) %>%
  filter(!is.na(Longitude)) 
leaflet() %>% addProviderTiles("Esri.NatGeoWorldMap") %>%
  
addCircles(data = Newark4311SampleAll,lng = ~Longitude, lat = ~Latitude, 
           color = ~c("red"))  %>%
  # controls
  setView(lng=center_lon, lat=center_lat,zoom = 15) 

library(leaflet.extras)
leaflet() %>% addProviderTiles("Esri.NatGeoWorldMap") %>%
  setView(lng=center_lon, lat=center_lat,zoom = 17) %>%
  addHeatmap( data = Newark4311SampleAll,
    lng = ~Longitude, lat = ~Latitude, 
    blur = 20, max = 0.05, radius = 15
) %>%
  addResetMapButton() %>%
  addSearchGoogle() %>%
  addFullscreenControl()

top10complaints = na.omit(till.2015) %>%
  group_by(Complaint) %>%
  summarise(Count = length(Location)) %>%
  arrange(desc(Count), .by_group = TRUE)
  

#as.factor(till.2015$Complaint)

grepl(paste(except, collapse = "|"),ab)

[1] TRUE

write.csv(til.2015, file = "till2015.csv",row.names=FALSE)

Error in is.data.frame(x) : object 'til.2015' not found

names(till.2015)

[1] "Complaint"   "ComplaintID" "Location"    "Longitude"   "Request Id"  "DateCreated" "Latitude"    "_id"         "ID"         

x2018$Latitude = geocoded$lat
x2018$Longitude = geocoded$long

Geocoded2018 %>%
  filter(!is.na(lat) ) %>%
  filter(!is.na(long)) %>%
leaflet() %>% addProviderTiles("Esri.NatGeoWorldMap") %>%
  
addMarkers(lng = ~long, lat = ~lat
           ,clusterOptions = markerClusterOptions())  %>%
  # controls
  setView(lng=center_lon, lat=center_lat,zoom = 15)